package org.esisar.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class StatisticsStatementCreator {

	private  PreparedStatement preparedStatement;
	
	public  PreparedStatement getNumberOfOrders(Connection connection) throws SQLException {
		preparedStatement = connection.prepareStatement("SELECT COUNT(*) AS numberoforders FROM Orders");		
		return preparedStatement;
	}

	public PreparedStatement getAveragePrice(Connection connection) throws SQLException {
		preparedStatement = connection.prepareStatement("SELECT AVG(SUM(quantity * price)) AS average FROM OrderLine GROUP BY idOrder");
		return preparedStatement;
	}
	
	public PreparedStatement getBestClient(Connection connection) throws SQLException {
		preparedStatement = connection.prepareStatement("SELECT surname "
				+ "FROM client "
				+ "WHERE idclient IN (SELECT idClient	"
				+ "FROM orders o, orderLine l "
				+ "WHERE o.idOrder = l.idOrder "
				+ "GROUP BY o.idClient, o.idOrder "
				+ "HAVING SUM(l.quantity * price) = (SELECT MAX(SUM(l.quantity * price)) "
				+ "FROM orders o, orderLine l "
				+ "WHERE o.idOrder = l.idOrder "
				+ "GROUP BY o.idClient, o.idOrder))");
		return preparedStatement;
	}
}
